﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DatabaseInterface;
using System.Data.SqlClient;


namespace BaseUnits.Builders
{
    public class PowerUnitBuilder
    {
        Connection connection;
        public PowerUnitBuilder(Connection connection)
        {
            this.connection = connection;
        }
        public List<IPowerUnit> getPowerUnits(String PowerPlantId = "")
        {
            String sqlCommandText;
            SqlConnection cnx = this.connection.getConnection();
            if (PowerPlantId == "")
            {
                sqlCommandText = "SELECT PowerUnitId, externalId, Description, BuildDate, isCDM FROM PowerUnits";
            }
            else
            {
                sqlCommandText = "SELECT PowerUnitId, externalId, Description, BuildDate, isCDM FROM PowerUnits WHERE PowerUnitID in ";
                sqlCommandText += "(SELECT PowerUnitID FROM PlantPowerUnits WHERE PlantId ='" + PowerPlantId + "')";
            }
            List<IPowerUnit> powerUnits = new List<IPowerUnit>();
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DatabaseInterface.DataBase.PowerUnits pu = new DatabaseInterface.DataBase.PowerUnits();
            da.Fill(pu._PowerUnits);
            
            DateTime buildDate;
            String description;
            int powerUnitID;
            String externalId;
            Boolean isCDM;
            PowerUnit newPu;

            cmd.Parameters.Add("@PowerUnitId", System.Data.SqlDbType.Int);

            DatabaseInterface.DataBase.PowerUnits puAux;
            
            foreach (DatabaseInterface.DataBase.PowerUnits.PowerUnitsRow row in pu._PowerUnits.Rows)
            {
                puAux = new DatabaseInterface.DataBase.PowerUnits();
                powerUnitID = Convert.ToInt32(row["PowerUnitID"]);
                description = Convert.ToString(row["Description"]);
                externalId = Convert.ToString(row["ExternalId"]);
                buildDate = Convert.ToDateTime(row["BuildDate"]);
                isCDM = Convert.ToBoolean(row["isCDM"]);

                cmd.CommandText = "SELECT * FROM PowerUnitPriority WHERE PowerUnitId = @PowerUnitId";
                cmd.Parameters["@PowerUnitId"].Value = powerUnitID;
                da.Fill(puAux.PowerUnitPriority);
                DatabaseInterface.DataBase.PowerUnits.PowerUnitPriorityDataTable priority = puAux.PowerUnitPriority;

                cmd.CommandText = "SELECT * FROM EmissionFactors WHERE PowerUnitId = @PowerUnitId";
                da.Fill(puAux.EmissionFactors);
                DatabaseInterface.DataBase.PowerUnits.EmissionFactorsDataTable emissions = puAux.EmissionFactors;

                cmd.CommandText = "SELECT * FROM PowerUnitProduction WHERE PowerUnitId = @PowerUnitId";
                da.Fill(puAux.Production);
                DatabaseInterface.DataBase.PowerUnits.ProductionDataTable production = puAux.Production;


                newPu = new PowerUnit(powerUnitID, description, externalId, buildDate, priority, emissions, production, isCDM);
                powerUnits.Add(newPu);
            }
            return powerUnits;
        }
        public int GetUnitId(String externalId)
        {
            int powerUnitId = -1;
            String sqlCommandText = "";
            SqlConnection cnx = this.connection.getConnection();
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);

            cmd.CommandText = "SELECT TOP 1 PowerUnitId FROM PowerUnits WHERE externalId = @externalId";
            
            cmd.Parameters.Add("@externalId", System.Data.SqlDbType.VarChar);
            cmd.Parameters["@externalId"].Value = externalId;
            
            try
            {
                SqlDataReader dr;
                cnx.Open();
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    powerUnitId = Convert.ToInt32(dr[0]);
                }
                cnx.Close();
            }
            catch (Exception ex)
            {
                if (cnx.State != System.Data.ConnectionState.Closed)
                {
                    cnx.Close();
                }
                throw ex;
            }
            return powerUnitId;
        }
        /// <summary>
        /// Persists Power Unit to database and returns True if records were affected
        /// </summary>
        /// <param name="powerUnit">If power unit does not exist, Id must be -1</param>
        /// <returns>If True, then records were afected</returns>
        public int PersistPowerUnit(IPowerUnit powerUnit)
        {
            int newPowerUnitId = 0;
            String sqlCommandText = "";
            SqlConnection cnx = this.connection.getConnection();
            SqlCommand cmd = new SqlCommand(sqlCommandText, cnx);
            
            if (powerUnit.ID == -1)
            {
                sqlCommandText = "INSERT INTO PowerUnits (externalId, Description, BuildDate, isCDM) VALUES (@externalId, @Description, @BuildDate, @isCDM)";
            }
            else
            {
                sqlCommandText = "UPDATE PowerUnits SET externalId = @externalId, Description = @Description, BuildDate = @BuildDate, isCDM = @isCDM WHERE PowerUnitID = @PowerUnitId";
                cmd.Parameters.Add("@PowerUnitId", System.Data.SqlDbType.Int);
                cmd.Parameters["@PowerUnitId"].Value = powerUnit.ID;
            }

            cmd.CommandText = sqlCommandText;

            cmd.Parameters.Add("@externalId", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@Description", System.Data.SqlDbType.VarChar);
            cmd.Parameters.Add("@BuildDate", System.Data.SqlDbType.Date);
            cmd.Parameters.Add("@isCDM", System.Data.SqlDbType.Bit);

            cmd.Parameters["@externalId"].Value = powerUnit.ExternalId;
            cmd.Parameters["@Description"].Value = powerUnit.Description;
            cmd.Parameters["@BuildDate"].Value = powerUnit.BuildDate;
            cmd.Parameters["@isCDM"].Value = powerUnit.IsCDM;

            try
            {
                cnx.Open();
                cmd.ExecuteNonQuery();
                cnx.Close();
                if (powerUnit.ID == -1)
                {
                    cmd.CommandText = "SELECT TOP 1 PowerUnitId FROM PowerUnits WHERE externalId = @externalId";
                    SqlDataReader dr;
                    cnx.Open();
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        newPowerUnitId = Convert.ToInt32(dr[0]);
                    }
                    cnx.Close();
                }
                else
                {
                    newPowerUnitId = powerUnit.ID;
                }
            }
            catch (Exception ex)
            {
                if (cnx.State != System.Data.ConnectionState.Closed)
                {
                    cnx.Close();
                }
                throw ex;
            }
            return newPowerUnitId;

        }
    }
}
